﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using NBA.Entity;
using System.Data.SqlClient;
using NBA.DBUtility;
using JJY.DBUtility;

namespace NBA.DataAccess
{
    public class DaMatchTeamData
    {
        NbaDbUtility nbaDbUtility = new NbaDbUtility();

        public int InsertOrUpdate(MatchTeamData matchTeamData)
        {
            int result = 0;
            MatchTeamData tempMatchTeamData = Find(matchTeamData.MatchId, matchTeamData.TeamId);
            if (tempMatchTeamData!=null)
            {
                result = Update(matchTeamData);
            }
            else
            {
                result = Insert(matchTeamData);
            }
            return result;
        }

        public MatchTeamData Find(Guid matchId, Guid teamId)
        {
            string sql = string.Format("SELECT * FROM MatchTeamData " +
                " WHERE matchId='{0}' AND teamId='{1}'", matchId, teamId);
            return NbaEntityHelper<MatchTeamData>.GetEntity(sql);
        }

        public int Update(MatchTeamData matchTeamData)
        {
            string sql = string.Format("UPDATE MatchTeamData SET IsAtHome=@IsAtHome,Shot2=@Shot2, " + 
                " Goal2=@Goal2,Shot3=@Shot3, FreeThrow=@FreeThrow,FreeThrowGoal=@FreeThrowGoal, " + 
                " OffensiveRebound=@OffensiveRebound,DefensiveRebound=@DefensiveRebound, " +
                " Assist=@Assist,Foul=@Foul,Steal=@Steal,Turnover=@Turnover,Block=@Block, " +
                " Scoring=@Scoring, UpdateTime=@UpdateTime " + 
                " WHERE matchId=@matchId AND teamId=@teamId");

            SqlParameter[] sqlParameterSet = 
            {
                new SqlParameter("@MatchId",matchTeamData.MatchId),
                new SqlParameter("@TeamId",matchTeamData.TeamId),
                new SqlParameter("@IsAtHome",matchTeamData.IsAtHome),
                new SqlParameter("@Shot2",matchTeamData.Shot2),
                new SqlParameter("@Goal2",matchTeamData.Goal2),
                new SqlParameter("@Shot3",matchTeamData.Shot3),
                new SqlParameter("@Goal3",matchTeamData.Goal3),
                new SqlParameter("@FreeThrow",matchTeamData.FreeThrow),
                new SqlParameter("@FreeThrowGoal",matchTeamData.FreeThrowGoal),
                new SqlParameter("@OffensiveRebound",matchTeamData.OffensiveRebound),
                new SqlParameter("@DefensiveRebound",matchTeamData.DefensiveRebound),
                new SqlParameter("@Assist",matchTeamData.Assist),
                new SqlParameter("@Foul",matchTeamData.Foul),
                new SqlParameter("@Steal",matchTeamData.Steal),
                new SqlParameter("@Turnover",matchTeamData.Turnover),
                new SqlParameter("@Block",matchTeamData.Block),
                new SqlParameter("@Scoring",matchTeamData.Scoring),
                new SqlParameter("@UpdateTime", System.DateTime.Now)
            };

            return nbaDbUtility.ExecuteNonQuery(sql, sqlParameterSet);
        }

        public int Insert(MatchTeamData matchTeamData)
        {
            string sql = "INSERT INTO MatchTeamData(MatchId,TeamId,IsAtHome,Shot2,Goal2, " +
                " Shot3,Goal3,FreeThrow,FreeThrowGoal,OffensiveRebound,DefensiveRebound,Assist,Foul, " +
                " Steal,Turnover,Block,Scoring) " +
                " VALUES(@MatchId,@TeamId,@IsAtHome,@Shot2,@Goal2,@Shot3,@Goal3,@FreeThrow,@FreeThrowGoal, " +
                " @OffensiveRebound,@DefensiveRebound,@Assist,@Foul,@Steal,@Turnover,@Block,@Scoring)";

            SqlParameter[] sqlParameterSet = 
            {
                new SqlParameter("@MatchId",matchTeamData.MatchId),
                new SqlParameter("@TeamId",matchTeamData.TeamId),
                new SqlParameter("@IsAtHome",matchTeamData.IsAtHome),
                new SqlParameter("@Shot2",matchTeamData.Shot2),
                new SqlParameter("@Goal2",matchTeamData.Goal2),
                new SqlParameter("@Shot3",matchTeamData.Shot3),
                new SqlParameter("@Goal3",matchTeamData.Goal3),
                new SqlParameter("@FreeThrow",matchTeamData.FreeThrow),
                new SqlParameter("@FreeThrowGoal",matchTeamData.FreeThrowGoal),
                new SqlParameter("@OffensiveRebound",matchTeamData.OffensiveRebound),
                new SqlParameter("@DefensiveRebound",matchTeamData.DefensiveRebound),
                new SqlParameter("@Assist",matchTeamData.Assist),
                new SqlParameter("@Foul",matchTeamData.Foul),
                new SqlParameter("@Steal",matchTeamData.Steal),
                new SqlParameter("@Turnover",matchTeamData.Turnover),
                new SqlParameter("@Block",matchTeamData.Block),
                new SqlParameter("@Scoring",matchTeamData.Scoring)
            };

            return nbaDbUtility.ExecuteNonQuery(sql, sqlParameterSet);
        }

        public List<MatchTeamData> GetMatchTeamData(Guid teamId, List<Guid> matchIdSet)
        {
            string sql = string.Format("SELECT * FROM MatchTeamData d" +
                " LEFT JOIN MatchInfo m ON d.MatchId = m.MatchId " +
                " WHERE d.TeamId='{0}' AND d.MatchId in ('{1}') ORDER BY m.MatchDateTime DESC", 
                teamId, string.Join("','",matchIdSet));
            return NbaEntityHelper<MatchTeamData>.GetEntitySet(sql);
        }

        public List<MatchTeamData> GetAllTeamAverage(int seazon)
        {
            string sql = string.Format(@"SELECT t.TeamId, CONVERT(SMALLINT,AVG(Shot2)) AS Shot2,
                CONVERT(SMALLINT,AVG(Goal2)) AS Goal2,CONVERT(SMALLINT,AVG(Shot3)) AS Shot3,
                CONVERT(SMALLINT,AVG(Goal3)) AS Goal3,CONVERT(SMALLINT,AVG(FreeThrow)) AS FreeThrow,
                CONVERT(SMALLINT,AVG(FreeThrowGoal)) AS FreeThrowGoal,
                CONVERT(SMALLINT,AVG(OffensiveRebound)) AS OffensiveRebound,
                CONVERT(SMALLINT,AVG(DefensiveRebound)) AS DefensiveRebound,
                CONVERT(SMALLINT,AVG(Assist)) AS Assist,CONVERT(SMALLINT,AVG(Foul)) AS Foul,
                CONVERT(SMALLINT,AVG(Steal)) AS Steal,CONVERT(SMALLINT,AVG(Turnover)) AS Turnover,
                CONVERT(SMALLINT,AVG(Block)) AS Block,CONVERT(SMALLINT,AVG(Scoring)) AS Scoring
                 FROM MatchTeamData t LEFT JOIN MatchInfo m ON t.MatchId = m.MatchId
                 WHERE SeasonYear={0} AND Scoring>0
                 GROUP BY t.TeamId", seazon);
            return NbaEntityHelper<MatchTeamData>.GetEntitySet(sql);
        }
    }
}
